﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Backend;
using System.Threading.Tasks;

namespace Data_Layer
{
    public class DBUsersBooking : IDBUsersBooking
    {
        SqlConnection con;
        List<UsersBooking> allUsersBooking = new List<UsersBooking>();
        List<UsersBooking> allUsersBookingByUsername = new List<UsersBooking>();

        public DBUsersBooking()
        {
            con = Connection.GetInstance().GetConnection();
        }

        public void createUsersBooking(UsersBooking usersbooking)
        {
            try
            {
                con.Open();
                String Query = "use Internship insert into UsersBooking values(" + "'" + usersbooking.UserName + "' , " + usersbooking.BookingNO  + ")";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with createUsersBooking()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public void deleteUsersBooking(string username)
        {
            try
            {
                con.Open();
                String Query = "use Internship delete from UsersBooking where username = " + "'" + username + "'";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with deleteUsersBooking()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }


        }

        public UsersBooking readUsersBooking(string username)
        {
            try
            {
                con.Open();
                UsersBooking usersbooking = new UsersBooking();
                String Query = "use Internship select * from UsersBooking where username =" + "'" + username + "'";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();

                while (reader.Read())
                {
                    usersbooking.UserName = (string)reader["username"];                    
                    usersbooking.BookingNO = (int)reader["bookingNO"];
                }

                reader.Close();
                return usersbooking;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with readUsersBooking()");
                Console.WriteLine(ex.Message);

                return null;

            }

            finally
            {
                con.Close();
            }
        }

        public List<UsersBooking> getallUsersBooking()
        {
            try
            {
                con.Open();
                String Query = "use Internship select * from UsersBooking";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    UsersBooking usersbooking = new UsersBooking();
                    usersbooking.UserName = (string)reader["username"];                    
                    usersbooking.BookingNO = (int)reader["bookingNO"];

                    allUsersBooking.Add(usersbooking);
                }

                reader.Close();

                return allUsersBooking;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with getallUsersBooking()");
                Console.WriteLine(ex.Message);

                return allUsersBooking;
            }

            finally
            {
                con.Close();
            }
        }

        public List<UsersBooking> getallUsersBookingbyUsername(string username)
        {
            try
            {
                con.Open();
                String Query = "use Internship select * from UsersBooking where username = " + "'" + username + "'";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    UsersBooking usersbooking = new UsersBooking();
                    usersbooking.UserName = (string)reader["username"];
                    usersbooking.BookingNO = (int)reader["bookingNO"];

                    allUsersBookingByUsername.Add(usersbooking);
                }

                reader.Close();

                return allUsersBookingByUsername;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with getallUsersBookingbyUsername()");
                Console.WriteLine(ex.Message);

                return allUsersBookingByUsername;
            }

            finally
            {
                con.Close();
            }
        }
    }

}
